﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlServerCe;
using PolePositionMobileUtilityGeneral;

namespace PolePositionMobileUtilityData
{
    public class DUtilityInspection
    {
        public static DataTable GetMainGrid()
        {
            DataTable _Dt = new DataTable();
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da = null;
            StringBuilder _sql = new StringBuilder();
            try
            {
                _Con = Utils.GetConnection();
                #region Query
                _sql.AppendLine("Select ");
                _sql.AppendLine("   U.UtilityInspectionId as Id, ");
                _sql.AppendLine("   R.Name as ResultType_Name , ");
                _sql.AppendLine("   I.Name as InspectionType_Name , ");
                _sql.AppendLine("   T.name as Toptype_Name, ");
                _sql.AppendLine("   Number, ");
                _sql.AppendLine("   UR.UtilityInspectionResultId as Result_Id, ");
                _sql.AppendLine("   UR.UtilityInspectionResultId as InspectionResults_Name ");
                _sql.AppendLine("FROM tb_UtilityInspections U ");
                _sql.AppendLine("   LEFT JOIN tb_Tops T ON U.ToptypeFk = T.TopId");
                _sql.AppendLine("   INNER JOIN tb_utilityInspectionResults UR ON UR.UtilityInspectionFk = U.UtilityInspectionId");
                _sql.AppendLine("   INNER JOIN tb_ResultTypes R ON UR.ResultTypeFk= R.ResultTypeId ");
                _sql.AppendLine("   INNER JOIN tb_InspectionTypes I ON UR.InspectionTypeFk = I.InspectionTypeId ");
                _sql.AppendLine(" ORDER BY AcquisitionDate Desc");
                #endregion Query
                _Da = new SqlCeDataAdapter(_sql.ToString(), _Con);
                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.Fill(_Dt);
                return _Dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }
        
        public static int Insert(int mapFk, double latitude, double longitude, int year, DateTime lVisit, string number, DateTime acquisitionDate, int treatmentFk, int classFk, int specieFk, int toptypeFk, int lengthFk, int inspectorFk, int companyFk,int sectionFk, int guardsQuantity, string lastCompany, int visitTypeFk, bool poleTag, bool billable, bool groundWireRepair)
        {
            DataTable _Dt = new DataTable();
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da = null;
            StringBuilder _sql = new StringBuilder();
            try
            {
                _Con = Utils.GetConnection();
                #region Query
                _sql.AppendLine("Insert into tb_UtilityInspections(MapFk,Latitude,Longitude,Year,LVisit,Number,AcquisitionDate,TreatmentFk,ClassFk,SpecieFk,ToptypeFk,LengthFk,InspectorFk,CompanyFk,SectionFk, GuardsQuantity, LastCompany, VisitTypeFk, PoleTag, Billable, GroundWireRepair) values( ");
                _sql.AppendLine("   @MapFk, ");
                _sql.AppendLine("   @Latitude, ");
                _sql.AppendLine("   @Longitude, ");
                _sql.AppendLine("   @Year, ");
                _sql.AppendLine("   @LVisit, ");
                _sql.AppendLine("   @Number, ");
                _sql.AppendLine("   @AcquisitionDate, ");
                _sql.AppendLine("   @TreatmentFk, ");
                _sql.AppendLine("   @ClassFk, ");
                _sql.AppendLine("   @SpecieFk, ");
                _sql.AppendLine("   @ToptypeFk, ");
                _sql.AppendLine("   @LengthFk, ");
                _sql.AppendLine("   @InspectorFk, ");
                _sql.AppendLine("   @CompanyFk, ");
                _sql.AppendLine("   @SectionFk, ");
                _sql.AppendLine("   @GuardsQuantity, ");
                _sql.AppendLine("   @LastCompany, ");
                _sql.AppendLine("   @VisitTypeFk, ");
                _sql.AppendLine("   @PoleTag, ");
                _sql.AppendLine("   @Billable, ");
                _sql.AppendLine("   @GroundWireRepair)");
                #endregion Query
                _Da = new SqlCeDataAdapter(_sql.ToString(), _Con);

                #region Values

                _Da.SelectCommand.Parameters.Add(Utils.CreateDecimalParameter("@Latitude", ParameterDirection.Input, false, 19, 6, latitude));
                _Da.SelectCommand.Parameters.Add(Utils.CreateDecimalParameter("@Longitude", ParameterDirection.Input, false, 19, 6, longitude));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@TopTypeFk", ParameterDirection.Input, false, toptypeFk));
                _Da.SelectCommand.Parameters.Add(Utils.CreateDateTimeParameter("@LVisit", ParameterDirection.Input, false, lVisit));
                _Da.SelectCommand.Parameters.Add(Utils.CreateDateTimeParameter("@AcquisitionDate", ParameterDirection.Input, false, acquisitionDate));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@InspectorFk", ParameterDirection.Input, false, inspectorFk));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@CompanyFk", ParameterDirection.Input, false, companyFk));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@MapFk", ParameterDirection.Input, false, mapFk != Int32.MinValue ? (object)mapFk : (object)DBNull.Value));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@SectionFk", ParameterDirection.Input, false, sectionFk != Int32.MinValue ? (object)sectionFk : (object)DBNull.Value));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, false, treatmentFk != Int32.MinValue ? (object)treatmentFk : (object)DBNull.Value));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@ClassFk", ParameterDirection.Input, false, classFk != Int32.MinValue ? (object)classFk : (object)DBNull.Value));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@SpecieFk", ParameterDirection.Input, false, specieFk != Int32.MinValue ? (object)specieFk : (object)DBNull.Value));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@LengthFk", ParameterDirection.Input, false, lengthFk != Int32.MinValue ? (object)lengthFk : (object)DBNull.Value));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@GuardsQuantity", ParameterDirection.Input, false, guardsQuantity != Int32.MinValue ? guardsQuantity : 0));
                _Da.SelectCommand.Parameters.Add(Utils.CreateNVarCharParameter("@LastCompany", 200, ParameterDirection.Input, false, lastCompany));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@VisitTypeFk", ParameterDirection.Input, false, visitTypeFk));
                _Da.SelectCommand.Parameters.Add(Utils.CreateBitParameter("@PoleTag", ParameterDirection.Input, false, poleTag));
                _Da.SelectCommand.Parameters.Add(Utils.CreateBitParameter("@Billable", ParameterDirection.Input, false, billable));
                _Da.SelectCommand.Parameters.Add(Utils.CreateBitParameter("@GroundWireRepair", ParameterDirection.Input, false, groundWireRepair));
                
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@Year", ParameterDirection.Input, false, year != Int32.MinValue ? (object)year : (object)DBNull.Value));
                _Da.SelectCommand.Parameters.Add(Utils.CreateNVarCharParameter("@Number",100, ParameterDirection.Input, false, !string.IsNullOrEmpty(number) ? (object)number : (object)DBNull.Value));

                #endregion

                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.SelectCommand.ExecuteNonQuery();

                _sql = new StringBuilder();
                _sql.AppendLine("SELECT     MAX(UtilityInspectionId) AS id FROM tb_UtilityInspections");

                _Da = new SqlCeDataAdapter(_sql.ToString(), _Con);
                _Da.SelectCommand.CommandType = CommandType.Text;
                return (Int32)_Da.SelectCommand.ExecuteScalar();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }

        }

        public static void Update(int inspectionId, int mapFk, double latitude, double longitude, int year, DateTime lVisit, string number, DateTime acquisitionDate, int treatmentFk, int classFk, int specieFk, int toptypeFk, int lengthFk, int inspectorFk, int companyFk,int  sectionFk, int guardsQuantity, string lastCompany, int visitTypeFk, bool poleTag, bool billable, bool groundWireRepair, SqlCeTransaction tr)
        {
            DataTable _Dt = new DataTable();
            SqlCeDataAdapter _Da = null;
            StringBuilder _sql = new StringBuilder();
            try
            {
                #region Query
                _sql.AppendLine("Update tb_UtilityInspections ");
                _sql.AppendLine("  SET Latitude = @Latitude, ");
                _sql.AppendLine("   MapFk = @MapFk, ");
                _sql.AppendLine("   Longitude = @Longitude, ");
                _sql.AppendLine("   Year = @Year, ");
                _sql.AppendLine("   LVisit = @LVisit, ");
                _sql.AppendLine("   Number = @Number, ");
                _sql.AppendLine("   AcquisitionDate = @AcquisitionDate, ");
                _sql.AppendLine("   TreatmentFk = @TreatmentFk, ");
                _sql.AppendLine("   ClassFk = @ClassFk, ");
                _sql.AppendLine("   SpecieFk = @SpecieFk, ");
                _sql.AppendLine("   ToptypeFk = @ToptypeFk, ");
                _sql.AppendLine("   LengthFk = @LengthFk, ");
                _sql.AppendLine("   InspectorFk = @InspectorFk, ");
                _sql.AppendLine("   CompanyFk = @CompanyFk, ");
                _sql.AppendLine("   SectionFk = @SectionFk, ");
                _sql.AppendLine("   GuardsQuantity = @GuardsQuantity, ");
                _sql.AppendLine("   LastCompany = @LastCompany, ");
                _sql.AppendLine("   PoleTag = @PoleTag, ");
                _sql.AppendLine("   Billable = @Billable, ");
                _sql.AppendLine("   GroundWireRepair = @GroundWireRepair, ");
                _sql.AppendLine("   VisitTypeFk = @VisitTypeFk ");
                _sql.AppendLine("Where UtilityInspectionId = @UtilityInspectionId");
                #endregion Query
                _Da = new SqlCeDataAdapter(_sql.ToString(), (SqlCeConnection)tr.Connection);

                #region Values

                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@UtilityInspectionId", ParameterDirection.Input, false, inspectionId));
                _Da.SelectCommand.Parameters.Add(Utils.CreateDecimalParameter("@Latitude", ParameterDirection.Input, false, 19, 6, latitude));
                _Da.SelectCommand.Parameters.Add(Utils.CreateDecimalParameter("@Longitude", ParameterDirection.Input, false, 19, 6, longitude));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@TopTypeFk", ParameterDirection.Input, false, toptypeFk));
                _Da.SelectCommand.Parameters.Add(Utils.CreateDateTimeParameter("@LVisit", ParameterDirection.Input, false, lVisit));
                _Da.SelectCommand.Parameters.Add(Utils.CreateDateTimeParameter("@AcquisitionDate", ParameterDirection.Input, false, acquisitionDate));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@InspectorFk", ParameterDirection.Input, false, inspectorFk));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@CompanyFk", ParameterDirection.Input, false, companyFk));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@MapFk", ParameterDirection.Input, false, mapFk != Int32.MinValue ? (object)mapFk : (object)DBNull.Value));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@SectionFk", ParameterDirection.Input, false, sectionFk != Int32.MinValue ? (object)sectionFk : (object)DBNull.Value));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, false, treatmentFk != Int32.MinValue ? (object)treatmentFk : (object)DBNull.Value));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@ClassFk", ParameterDirection.Input, false, classFk != Int32.MinValue ? (object)classFk : (object)DBNull.Value));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@SpecieFk", ParameterDirection.Input, false, specieFk != Int32.MinValue ? (object)specieFk : (object)DBNull.Value));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@LengthFk", ParameterDirection.Input, false, lengthFk != Int32.MinValue ? (object)lengthFk : (object)DBNull.Value));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@GuardsQuantity", ParameterDirection.Input, false, guardsQuantity != Int32.MinValue ? guardsQuantity : 0));
                _Da.SelectCommand.Parameters.Add(Utils.CreateNVarCharParameter("@LastCompany", 200, ParameterDirection.Input, false, lastCompany));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@VisitTypeFk", ParameterDirection.Input, false, visitTypeFk));
                _Da.SelectCommand.Parameters.Add(Utils.CreateBitParameter("@PoleTag", ParameterDirection.Input, false, poleTag));
                _Da.SelectCommand.Parameters.Add(Utils.CreateBitParameter("@Billable", ParameterDirection.Input, false, billable));
                _Da.SelectCommand.Parameters.Add(Utils.CreateBitParameter("@GroundWireRepair", ParameterDirection.Input, false, groundWireRepair));

                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@Year", ParameterDirection.Input, false, year != Int32.MinValue ? (object)year : (object)DBNull.Value));
                _Da.SelectCommand.Parameters.Add(Utils.CreateNVarCharParameter("@Number",100, ParameterDirection.Input, false, !string.IsNullOrEmpty(number) ? (object)number : (object)DBNull.Value));


                #endregion


                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.SelectCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
         }

        public static void Delete(int inspectionId, SqlCeTransaction tr)
        {
            StringBuilder _Sql = new StringBuilder(); ;
            SqlCeCommand _Cmd;
            #region Query
            _Sql.AppendLine("Delete tb_UtilityInspections where UtilityInspectionId = @UtilityInspectionId");
            #endregion Query
            _Cmd = new SqlCeCommand(_Sql.ToString(), (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            #region Parameters
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@UtilityInspectionId", ParameterDirection.Input, false, inspectionId));
            #endregion Parameters
            _Cmd.ExecuteNonQuery();
        }

        public static DataRow GetById(int inspectionId)
        {
            DataTable _Dt = new DataTable();
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da = null;
            StringBuilder _sql = new StringBuilder();
            try
            {
                _Con = Utils.GetConnection();
                #region Query
                _sql.AppendLine("Select ");
                _sql.AppendLine("   U.UtilityInspectionId, ");
                _sql.AppendLine("   U.AcquisitionDate, ");
                _sql.AppendLine("   U.MapFk, ");
                _sql.AppendLine("   U.SectionFk, ");
                _sql.AppendLine("   MA.Name as Map_Name, ");
                _sql.AppendLine("   U.Latitude, ");
                _sql.AppendLine("   U.Longitude, ");
                _sql.AppendLine("   U.TreatmentFk, ");
                _sql.AppendLine("   TRE.LookupValue as Treatment_Name, ");
                _sql.AppendLine("   U.ClassFk, ");
                _sql.AppendLine("   CLA.LookupValue as Class_Name, ");
                _sql.AppendLine("   U.SpecieFk, ");
                _sql.AppendLine("   E.LookupValue as Specie_Name, ");
                _sql.AppendLine("   U.ToptypeFk, ");
                _sql.AppendLine("   T.name as Toptype_Name, ");
                _sql.AppendLine("   U.LengthFk, ");
                _sql.AppendLine("   L.Name as Length_Name, ");
                _sql.AppendLine("   U.Year, ");
                _sql.AppendLine("   U.LVisit, ");
                _sql.AppendLine("   U.Number, ");
                _sql.AppendLine("   U.InspectorFk, ");
                _sql.AppendLine("   I.Name as Inspector_Name, ");
                _sql.AppendLine("   U.CompanyFk, ");
                _sql.AppendLine("   U.GuardsQuantity, ");
                _sql.AppendLine("   U.PoleTag, ");
                _sql.AppendLine("   U.Billable, ");
                _sql.AppendLine("   U.GroundWireRepair, ");
                _sql.AppendLine("   U.LastCompany, ");
                _sql.AppendLine("   U.VisitTypeFk, ");
                _sql.AppendLine("   VT.LookupValue as VisitType_Name, ");
                _sql.AppendLine("   C.Name as Company_Name ");
                _sql.AppendLine("FROM tb_UtilityInspections U ");
                _sql.AppendLine("   LEFT JOIN tb_Tops T ON U.ToptypeFk = T.TopId");
                _sql.AppendLine("   INNER JOIN tb_Companies C ON U.CompanyFk = C.CompanyId");
                _sql.AppendLine("   INNER JOIN tb_Inspectors I ON U.InspectorFk = I.InspectorId");
                _sql.AppendLine("   LEFT JOIN tb_Lookups TRE ON (U.TreatmentFk = TRE.LookupValue AND TRE.LookupTypeFk = 'Treatments' )");
                _sql.AppendLine("   LEFT JOIN tb_Lookups CLA ON (U.ClassFk = CLA.LookupValue AND CLA.LookupTypeFk = 'Classes' )");
                _sql.AppendLine("   LEFT JOIN tb_Lookups E ON (U.SpecieFk = E.LookupValue AND E.LookupTypeFk = 'Species' )");
                _sql.AppendLine("   LEFT JOIN tb_Lookups VT ON (U.VisitTypeFk = VT.LookupValue AND VT.LookupTypeFk = 'VisitTypes' )");
                _sql.AppendLine("   LEFT JOIN tb_Lengths L ON U.LengthFk = L.LengthId");
                _sql.AppendLine("   LEFT JOIN tb_Maps MA ON U.MapFk = MA.MapId ");
                _sql.AppendLine("Where U.UtilityInspectionId = @UtilityInspectionId ");
                #endregion Query
                _Da = new SqlCeDataAdapter(_sql.ToString(), _Con);
                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@UtilityInspectionId", ParameterDirection.Input, false, inspectionId));
                _Da.Fill(_Dt);
                if (_Dt.Rows.Count > 0)
                    return _Dt.Rows[0];
                else
                    return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }
    }
}
